-------------------------------------------------------------------------------
             Table "valid_testtypes"
       Attribute       |     Type     | Modifier
-----------------------+--------------+----------
 v_ttyp_code           | varchar(100) | not null
 v_ttyp_description    | varchar(50)  | not null
 v_ttyp_classification | varchar(15)  | not null
 v_ttyp_status         | char(1)      | not null
 v_ttyp_status_date    | timestamp    | not null
 v_ttyp_user_created   | varchar(30)  | not null
 v_ttyp_date_created   | timestamp    | not null
 v_ttyp_user_modified  | varchar(30)  |
 v_ttyp_date_modified  | timestamp    |
 v_ttyp_reptype        | varchar(10)  | not null
Index: valid_testtypes_pkey

 select v_ttyp_code from valid_testtypes ;
        v_ttyp_code
----------------------------
 XRAY
 SCAN
 ECG
 PT
 CTG
 Bio-Chemistry
 Biopsy
 Clinical Pathology
 Complete Haematogram
 Endocrinology
 Haematology


             Table "allied_medical_req_main"
           Attribute           |     Type     | Modifier
-------------------------------+--------------+----------
 amr_sequence_num              | bigint       | not null
 amr_id                        | bigint       | not null
 pat_reg_no                    | bigint       | not null
 visit_id                      | bigint       |
 emp_referral                  | varchar(30)  |
 v_eref_id                     | bigint       |
 amr_date_created              | timestamp    | not null
 amr_user_created              | varchar(30)  | not null
 amr_date_modified             | timestamp    |
 amr_user_modified             | varchar(30)  |
 amr_complete_flag             | char(1)      | not null
 amr_report_req_flag           | char(1)      |
 amr_film_req_flag             | char(1)      |
 amr_request_date              | varchar(10)  | not null
 amr_request_time              | varchar(12)  | not null
 amr_remarks                   | varchar(200) |
 amr_report_to_be_collected_on | timestamp    | not null
 amr_report_collected_on       | timestamp    |
 v_ttyp_code                   | varchar(30)  | not null
Indices: allied_medical_req_main_pkey,
         ind_amr_v_ttyp_code,
         uk_amr_id_v_ttyp_code

Instance #1
-----------
 explain select * from allied_medical_req_main where v_ttyp_code = 'XRAY'
mnh_hospdb-# ;
NOTICE:  QUERY PLAN:

Index Scan using ind_amr_v_ttyp_code on allied_medical_req_main  (cost=0.00..20.35 row
s=22 width=192)

EXPLAIN


Instance #2
-----------
 explain select * from allied_medical_req_main where v_ttyp_code = 'XRAY';
NOTICE:  QUERY PLAN:

Seq Scan on allied_medical_req_main  (cost=0.00..79.49 rows=713 width=192)

EXPLAIN


-------------------------------- 0000000000 --------------------------------------


As you can see from Instance #1 and #2 above, the usage of Index is not consistent. Do you
have any suggestions? 

Thanks,

Subra

P.S: I also did vacuum on the database. However, I am not clear as to what actually it does.